"""
Database Configuration and Connection Management
Handles unified database setup for both business and extractor data
"""

import asyncio
import logging
from typing import AsyncGenerator
from sqlalchemy import create_engine, MetaData, text
from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine, async_sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, Session

from app.core.config import settings

logger = logging.getLogger(__name__)

# Database engine for synchronous operations - unified database
unified_engine = create_engine(
    f"sqlite:///{settings.UNIFIED_DB_PATH}",
    echo=settings.DEBUG,
    connect_args={"check_same_thread": False}
)

# Session factory for unified database
UnifiedSessionLocal = sessionmaker(
    autocommit=False,
    autoflush=False,
    bind=unified_engine
)

# Async engine for unified database (using aiosqlite)
async_unified_engine = create_async_engine(
    f"sqlite+aiosqlite:///{settings.UNIFIED_DB_PATH}",
    echo=settings.DEBUG,
    connect_args={"check_same_thread": False}
)

# Async session factory for unified database
AsyncUnifiedSessionLocal = async_sessionmaker(
    async_unified_engine,
    class_=AsyncSession,
    expire_on_commit=False
)

# Base class for all models
Base = declarative_base()

# Legacy aliases for backward compatibility
BusinessBase = Base
ExtractorBase = Base
BusinessSessionLocal = UnifiedSessionLocal
ExtractorSessionLocal = UnifiedSessionLocal
AsyncBusinessSessionLocal = AsyncUnifiedSessionLocal
AsyncExtractorSessionLocal = AsyncUnifiedSessionLocal
business_engine = unified_engine
extractor_engine = unified_engine
async_business_engine = async_unified_engine
async_extractor_engine = async_unified_engine


# Dependency functions for FastAPI - unified database
async def get_business_db() -> AsyncGenerator[AsyncSession, None]:
    """Get business database session (unified database)"""
    async with AsyncUnifiedSessionLocal() as session:
        try:
            yield session
        finally:
            await session.close()


async def get_extractor_db() -> AsyncGenerator[AsyncSession, None]:
    """Get extractor database session (unified database)"""
    async with AsyncUnifiedSessionLocal() as session:
        try:
            yield session
        finally:
            await session.close()


async def get_unified_db() -> AsyncGenerator[AsyncSession, None]:
    """Get unified database session"""
    async with AsyncUnifiedSessionLocal() as session:
        try:
            yield session
        finally:
            await session.close()


def get_sync_business_db() -> Session:
    """Get synchronous business database session (unified database)"""
    return UnifiedSessionLocal()


def get_sync_extractor_db() -> Session:
    """Get synchronous extractor database session (unified database)"""
    return UnifiedSessionLocal()


def get_sync_unified_db() -> Session:
    """Get synchronous unified database session"""
    return UnifiedSessionLocal()


async def init_databases():
    """Initialize unified database connection and create tables if needed"""
    try:
        # Test unified database connection
        async with AsyncUnifiedSessionLocal() as session:
            await session.execute(text("SELECT 1"))
            logger.info(f"Unified database connected: {settings.UNIFIED_DB_PATH}")

        # Create all database tables if they don't exist
        from app.models.business import User, Team, TeamFileAssignment, ActivityLog, TelemetryAnalysis
        Base.metadata.create_all(bind=unified_engine)
        logger.info("Unified database tables initialized")

        logger.info("Database initialized successfully")

    except Exception as e:
        logger.error(f"Failed to initialize database: {e}")
        raise


async def close_databases():
    """Close unified database connection"""
    await async_unified_engine.dispose()
    logger.info("Database connection closed")


class DatabaseManager:
    """Database manager for unified database operations"""

    @staticmethod
    async def test_connections() -> dict:
        """Test unified database connection"""
        results = {}

        try:
            async with AsyncUnifiedSessionLocal() as session:
                await session.execute(text("SELECT 1"))
                results["unified"] = "connected"
        except Exception as e:
            results["unified"] = f"error: {e}"

        return results

    @staticmethod
    async def get_table_info() -> dict:
        """Get information about unified database tables"""
        info = {
            "unified_db": {"path": settings.UNIFIED_DB_PATH, "tables": []}
        }

        try:
            async with AsyncUnifiedSessionLocal() as session:
                result = await session.execute(
                    text("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
                )
                info["unified_db"]["tables"] = [row[0] for row in result.fetchall()]
        except Exception as e:
            info["unified_db"]["error"] = str(e)

        return info